Postgresql 的先進資料型態 JSON 與 JSONB

JSON 近年來應用相當廣泛.除了 Postgresql 以外,許多資料庫產品也都將其
列入資料型態.
在 ANSI SQL 2016 標準中,JSON 也已經列入標準.
https://en.wikipedia.org/wiki/SQL:2016

在 Postgresql 中, 目前提供兩種, JSON 及 JSONB.
在 2019-09-26 發布了 Postgresql 12 RC1, 在 Postgresql 12 中,
有令人注目的特色 JSON Path. 甚至可以說是 12版最主要的新功能.
預計在 2020年會發展成完整的 SQL/JSON.

我們先來看一些基本的 JSON 操作. 
輸入部分

純量
select '"abc"'::jsonb
     , '5'::jsonb;
    
+-------+-------+
| jsonb | jsonb |
+-------+-------+
| "abc" | 5     |
+-------+-------+

Array 方式
select '[1,2,"foo",null]'::json
     , '["abc","def","測試"]'::json
     , '["abc","def","測試"]'::jsonb;

+------------------+----------------------+------------------------+
|       json       |         json         |         jsonb          |
+------------------+----------------------+------------------------+
| [1,2,"foo",null] | ["abc","def","測試"] | ["abc", "def", "測試"] |
+------------------+----------------------+------------------------+

Key:Value 方式

select '{"1": "天津風", "2": "雪風", "3": "島風"}'::jsonb;

+-------------------------------------------+
|                   jsonb                   |
+-------------------------------------------+
| {"1": "天津風", "2": "雪風", "3": "島風"} |
+-------------------------------------------+

也可以任意的巢狀組合

SELECT '{"foo": [true, "bar"], "tags": {"a": 1, "b": null}}'::json;

+-----------------------------------------------------+
|                        json                         |
+-----------------------------------------------------+
| {"foo": [true, "bar"], "tags": {"a": 1, "b": null}} |
+-----------------------------------------------------+

接著來看運算子
select oprleft::regtype
     , oprname
     , oprright::regtype
     , oprresult::regtype
     , oprcode::regproc
  from pg_catalog.pg_operator
 where oprleft::regtype = ANY(ARRAY['json'::regtype, 'jsonb'::regtype])
    or oprright::regtype = ANY(ARRAY['json'::regtype, 'jsonb'::regtype])
    or oprresult::regtype = ANY(ARRAY['json'::regtype, 'jsonb'::regtype])
 order by oprleft, oprname, oprright, oprresult;

+---------+---------+----------+-----------+--------------------------+
| oprleft | oprname | oprright | oprresult |         oprcode          |
+---------+---------+----------+-----------+--------------------------+
| json    | #>      | text[]   | json      | json_extract_path        |
| json    | #>>     | text[]   | text      | json_extract_path_text   |
| json    | ->      | integer  | json      | json_array_element       |
| json    | ->      | text     | json      | json_object_field        |
| json    | ->>     | integer  | text      | json_array_element_text  |
| json    | ->>     | text     | text      | json_object_field_text   |
| jsonb   | #-      | text[]   | jsonb     | jsonb_delete_path        |
| jsonb   | #>      | text[]   | jsonb     | jsonb_extract_path       |
| jsonb   | #>>     | text[]   | text      | jsonb_extract_path_text  |
| jsonb   | -       | integer  | jsonb     | pg_catalog.jsonb_delete  |
| jsonb   | -       | text     | jsonb     | pg_catalog.jsonb_delete  |
| jsonb   | -       | text[]   | jsonb     | pg_catalog.jsonb_delete  |
| jsonb   | ->      | integer  | jsonb     | jsonb_array_element      |
| jsonb   | ->      | text     | jsonb     | jsonb_object_field       |
| jsonb   | ->>     | integer  | text      | jsonb_array_element_text |
| jsonb   | ->>     | text     | text      | jsonb_object_field_text  |
| jsonb   | <       | jsonb    | boolean   | jsonb_lt                 |
| jsonb   | <=      | jsonb    | boolean   | jsonb_le                 |
| jsonb   | <>      | jsonb    | boolean   | jsonb_ne                 |
| jsonb   | <@      | jsonb    | boolean   | jsonb_contained          |
| jsonb   | =       | jsonb    | boolean   | jsonb_eq                 |
| jsonb   | >       | jsonb    | boolean   | jsonb_gt                 |
| jsonb   | >=      | jsonb    | boolean   | jsonb_ge                 |
| jsonb   | ?       | text     | boolean   | jsonb_exists             |
| jsonb   | ?&      | text[]   | boolean   | jsonb_exists_all         |
| jsonb   | ?|      | text[]   | boolean   | jsonb_exists_any         |
| jsonb   | @>      | jsonb    | boolean   | jsonb_contains           |
| jsonb   | ||      | jsonb    | jsonb     | jsonb_concat             |
+---------+---------+----------+-----------+--------------------------+
(28 rows)

相關函數非常的多,簡易查法是在 psql 下 使用 \df json*

會出現非常多的函數,有處理,轉換,還有聚合等類型函數.

來看 jsonb_pretty

select jsonb_pretty('{"id": "陽炎九", "tags": {"name": "天津風", "other": "新型高溫高壓鍋爐"}
       , "武裝": ["四連裝(酸素)魚雷","五寸連裝炮"]}'::jsonb);

+-------------------------------------+
|            jsonb_pretty             |
+-------------------------------------+
| {                                  +|
|     "id": "陽炎九",                +|
|     "tags": {                      +|
|         "name": "天津風",          +|
|         "other": "新型高溫高壓鍋爐"+|
|     },                             +|
|     "武裝": [                      +|
|         "四連裝(酸素)魚雷",        +|
|         "五寸連裝炮"               +|
|     ]                              +|
| }                                   |
+-------------------------------------+

因為 Postgresql 提供了大量豐富的JSON支持,若要一一介紹,勢必要一系列才足夠.
在此僅先做基本的介紹.